package com.eorchis.util.excel;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

//import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
//import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
//import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 /**
 * Title: ExcelUtil<br>
 * Description: Excel工具类<br>
 * Copyright @ 2011~2015 Goldgov .All rights reserved.<br>
 * 
 * @author LuanKW
 * @createDate 2015-5-11
 * @version $Revision: $
 */
@SuppressWarnings({"rawtypes", "unchecked"})
public class ExcelUtil {

	public static ExcelImportResult excelParsing(InputStream is,Integer startRow,Class c) throws Exception {
		ExcelImportResult excelImportResult = excelParsing(is, 1, startRow, c);
		return excelImportResult;
	}
	/**
	 * 将InputStram解析为指定类型对象集合，记录解析过程中的异常信息并汇总
	 * @param is
	 * @param sheetIndex 要解析的工作表索引，从1开始
	 * @param startRow 数据起始行索引，从1开始
	 * @param c
	 * @return
	 * @throws Exception
	 */
	public static ExcelImportResult excelParsing(InputStream is,Integer sheetIndex,Integer startRow,Class c) throws Exception {
		ExcelImportResult result = new ExcelImportResult();
		// 获得导入结果集
		Workbook wb = ExcelUtil.createWorkbook(is);
		// 只解析第一个工作表中的内容
		Sheet sheet = wb.getSheetAt(sheetIndex - 1);
		List<? extends ExcelBean> resultList = doParsing(sheet, startRow, c);
		result.setResultList(resultList);
		// 循环遍历异常信息
		boolean resultSign = true;
		for(ExcelBean bean : resultList){
			// 出现异常信息
			if(!"".equals(bean.getErrorMessage().toString())){
				resultSign = false;
				result.getErrorMessage().append(bean.getErrorMessage().toString());
			}
		}
		result.setResultSign(resultSign);
		return result;
	}

	/**
	 * 将InputStream解析为指定对象
	 * 
	 * @param is
	 * @param startRow
	 * @return
	 * @throws Exception
	 */
	private static List<? extends ExcelBean> doParsing(Sheet sheet,Integer startRow,Class c) throws Exception {
		// 得到当前工作表的总行数，次行数从0开始计算
		int lastRow = sheet.getLastRowNum();
		List<ExcelBean> excelBeanList = new ArrayList<ExcelBean>();
		// 行解析，startRow属性定义了数据从该行开始解析，程序中定义的行数从1开始算，所以此处要-1
		for (int rowIndex = startRow-1; rowIndex <= lastRow; rowIndex++) {
			Row row = sheet.getRow(rowIndex); // 得到 第 n 行
			if(row == null){
				continue;
			}
			ExcelBean excelBean = getCellValue(row, rowIndex,c); //解析对象
			if(excelBean!=null){
				excelBeanList.add(excelBean);
			}
		}
		return excelBeanList;
	}
	
	/**
	 * 针对Excel中的一行，进行详细的列解析。填充对象
	 * 
	 * @param row
	 * @param rowIndex
	 * @return
	 * @throws Exception 
	 */
	private static ExcelBean getCellValue(Row row, int rowIndex,Class c) throws Exception {
		// 获得列总数
		//int lastCellNum = row.getLastCellNum();
		// 目标对象类型
		Class<? extends ExcelBean> excelClass =  (Class<? extends ExcelBean>) Class.forName(c.getName());
		// 实例化获得对象内必要信息，即解析规则
		ExcelBean excelBean = excelClass.newInstance();
		// 获得解析规则
		Map<Integer,String> cellWithPropertyMap = excelBean.getCellWithPropertyMap();
		Map<String,ExcelRules> rulesMap = excelBean.getRulesMap();
		ExcelRules excelRules;
		for (int cellIndex = 0; cellIndex < rulesMap.size(); cellIndex++) {
			// 获得序号对应属性名称，Excel中的列数从0开始计算而程序中从1开始，所以此处+1
			String field = cellWithPropertyMap.get(cellIndex+1);
			// 无法找到cell对应的属性
			if(field==null||"".equals(field)){
				continue;
			}
			excelRules = rulesMap.get(field);
			String customErrorMessage = excelRules.getErrorMessage();
			// 获得属性对应解析规则
			excelRules = rulesMap.get(field);
			Cell cell = row.getCell(cellIndex);
			// 列为空时需要判断程序中定义其是否为必填
			if(cell==null){
				if(excelRules.isNullAble()){
					continue;
				}else{
					if(customErrorMessage!=null&&!"".equals(customErrorMessage)){
						excelBean.getErrorMessage().append("【"+row.getSheet().getSheetName()+"】工作表，【"+(rowIndex+1)+"】行，【"+(cellIndex+1)+"】列："+customErrorMessage+excelBean.errorMessageSplit());
					}else{
						excelBean.getErrorMessage().append("【"+row.getSheet().getSheetName()+"】工作表，【"+(rowIndex+1)+"】行，【"+(cellIndex+1)+"】列：此单元格的数据不能为空值"+excelBean.errorMessageSplit());
					}
					continue;
				}
			}
			// 将任何格式的内容均转换为String类型
			String value = getCellValue(cell);
			// 执行校验
			if(ExcelRules.check(value, excelRules, rowIndex, cellIndex, excelBean)){
				// 赋值
				setFieldValueObj(excelBean, field, value,excelRules);
			}
		}
		return excelBean;
	}
	
	/**
	 * 得到当前列的值
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellValue(Cell cell) {
		String typeString = "";
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			typeString = String.valueOf(cell.getNumericCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			typeString = String.valueOf(cell.getStringCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
			typeString = String.valueOf(cell.getDateCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
			typeString = String.valueOf(cell.getStringCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			typeString = "";
		}
		return typeString;
	}
	
	/**
	 * 设置对象的指定属性值
	 * 
	 * @param o
	 *            待反射的对象
	 * @param field
	 *            待反射的属性名
	 * @param value
	 *            填充值
	 * @throws Exception
	 * @author LuanKW
	 */
	private static void setFieldValueObj(Object o, String field, String value, ExcelRules excelRules)
			throws Exception {
		// 根据属性类型将value转换为相应的数据
		Object typeValue = value;
		if(excelRules.getFieldType() == Integer.class){
			// 整形转换时只考虑整形部分的数据
			typeValue = new Integer(new Double(Double.parseDouble(value)).intValue());
		}else if(excelRules.getFieldType() == Double.class){
			// 浮点型时转换全部数据
			typeValue = new Double(Double.parseDouble(value));
		}else if(excelRules.getFieldType() == Date.class){
			String dateFormat = excelRules.getDateFormat();
			SimpleDateFormat sf = new SimpleDateFormat(dateFormat);
			typeValue = sf.parse(value);
		}
		Class<? extends Object> clazz = o.getClass();
		Method setMethod = null;
		setMethod = clazz.getMethod(catchMethodName("set", field),new Class[] {typeValue.getClass()});
		setMethod.invoke(o, new Object[] { typeValue });
	}
	
	/**
	 * 获得对象方法名
	 * 
	 * @param getterOrSetter
	 * @param fieldName
	 * @return
	 * @author LuanKW
	 */
	private static String catchMethodName(String getterOrSetter,
			String fieldName) {
		char[] charArray = fieldName.toCharArray();
		charArray[0] = Character.toUpperCase(charArray[0]);
		return getterOrSetter + new String(charArray);
	}
	
	/**
	 * 解析不同版本的Excel文件
	 * 
	 * @param in
	 * @return
	 * @throws IOException
	 * @throws InvalidFormatException
	 */
	public static Workbook createWorkbook(InputStream in) throws IOException {
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            return new HSSFWorkbook(in);
        }
        /*if (POIXMLDocument.hasOOXMLHeader(in)) {
            return new XSSFWorkbook(OPCPackage.open(in));
        }*/
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }
}
